{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "import requests\n",
    "import PyPDF2\n",
    "import random"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# D:/Working/私活/保理合同/url合同模板.xlsx\n",
    "# 处理空值和None值\n",
    "excel_path = input(\"请入完整excel路径(请以'/'分割)：\")\n",
    "df = pd.read_excel(excel_path)\n",
    "df.dropna(inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>loan_application_no</th>\n",
       "      <th>保理合同</th>\n",
       "      <th>分期支付协议</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LN20210930230141334B7B43DC9ED46</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LN20210930231229446436208C4FBA9</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LN2021100111181824924236E86CD39</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LN202110011421043634D3F3FFBF5E5</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LN20211001142107022D4479A0DD011</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "      <td>https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               loan_application_no  \\\n",
       "0  LN20210930230141334B7B43DC9ED46   \n",
       "1  LN20210930231229446436208C4FBA9   \n",
       "2  LN2021100111181824924236E86CD39   \n",
       "3  LN202110011421043634D3F3FFBF5E5   \n",
       "4  LN20211001142107022D4479A0DD011   \n",
       "\n",
       "                                                保理合同  \\\n",
       "0  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...   \n",
       "1  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...   \n",
       "2  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...   \n",
       "3  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...   \n",
       "4  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...   \n",
       "\n",
       "                                              分期支付协议  \n",
       "0  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "1  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "2  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "3  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  \n",
       "4  https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.c...  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费一次性收取）.pdf?versionId=CAEQIBiBgIDG0pfq4RciIGFlZDYwZWQ1MGRmNDQ1MTFhZmQ3Yjg4MjViMjU5N2Vm\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-09-30/分期支付协议.pdf?versionId=CAEQIBiBgIDF9fLc4RciIGVhYjYyMWZjYmNlZDQ4OTA4NWMxNzk5ZjJlMTYzNGZl\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-09-30/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgIC5sqfd4RciIDRlOThiNjcyYjI2NTQ0MWM5MTBlYmJmMTgxYjJmMzQ2\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-09-30/分期支付协议.pdf?versionId=CAEQIBiBgIDX14bd4RciIGRkNDViYmVmMDFjZDQ0NzU5NTA0ZGM5NGVlMjBiMmI0\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgMDP3c3n4RciIGU3MzZlMzBhM2FmMTRjM2E4NjBlNzliYmExN2RkZGFl\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/分期支付协议.pdf?versionId=CAEQIBiBgMDH1rfn4RciIDU2MjJhOTE4M2FiNzQyOTE5NzgxYzljMWM1Njk3MWQ3\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgMCv.Jfq4RciIDg3N2I1NWVkZmU4MzQ3ZDI5YTFiNTMxNDIxMGQ1N2I5\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/分期支付协议.pdf?versionId=CAEQIBiBgICxr4bq4RciIGJjNjVmMjhiMWY1YzQ5ZThiYmQ3MTQ4ZDVkN2Y2NmNj\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/保理合同（保理手续费分期收取）.pdf?versionId=CAEQIBiBgIDQ5Zfq4RciIGYxZWJiNWY1OWNiNTRlZTI4NTU3MGQxMzYzYTEzMzUx\n",
      "https://xxt-amp-oss.oss-cn-hangzhou.aliyuncs.com/persistence/2021-10-01/分期支付协议.pdf?versionId=CAEQIBiBgICku4bq4RciIGFjNjEwYzdjMDE2MzQzZDk5MWZhNTVhNGNlMjgxOTNl\n"
     ]
    }
   ],
   "source": [
    "# 保存pdf文件\n",
    "os.makedirs('./baoli/',exist_ok=True)\n",
    "os.makedirs('./fenqi/',exist_ok=True)\n",
    "for i in df.index:\n",
    "    baoli_url = df.loc[i,'保理合同']#.split('pdf?')[0].__add__('pdf')\n",
    "    fenqi_url = df.loc[i,'分期支付协议']#.split('pdf?')[0].__add__('pdf')\n",
    "    baoli_name = df.loc[i,'保理合同'].split('.pdf?')[0].replace('/','-').split('-')[-1].__add__('.pdf')\n",
    "    fenqi_name = df.loc[i,'分期支付协议'].split('.pdf?')[0].replace('/','-').split('-')[-1].__add__('.pdf')\n",
    "    \n",
    "    loan_application_no = df.loc[i,'loan_application_no']\n",
    "    baoli_name = str(loan_application_no)+'_'+baoli_name\n",
    "    fenqi_name = str(loan_application_no)+'_'+fenqi_name\n",
    "    print(baoli_url)\n",
    "    print(fenqi_url)\n",
    "    res1 = requests.get(baoli_url)\n",
    "    res2 = requests.get(fenqi_url)\n",
    "\n",
    "    with open('./baoli/'+baoli_name,'wb') as file:\n",
    "        file.write(res1.content)\n",
    "\n",
    "    with open('./fenqi/'+fenqi_name,'wb') as file:\n",
    "        file.write(res2.content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['贷款申请编号', '保理合同编号', '保理合同签署日期', '基础合同/协议编号', '基础合同/协议名称'], ['LN20210930230141334B7B43DC9ED46', '2109302200000057-B', '2021-09-30', '2109302200000057-F', '《分期支付协议》'], ['LN20210930231229446436208C4FBA9', '2109302000000008-B', '2021-09-30', '2109302000000008-F', '《分期支付协议》'], ['LN2021100111181824924236E86CD39', '2110011100000243-B', '2021-10-01', '2110011100000243-F', '《分期支付协议》'], ['LN202110011421043634D3F3FFBF5E5', '2110011100000871-B', '2021-10-01', '2110011100000871-F', '《分期支付协议》'], ['LN20211001142107022D4479A0DD011', '2110011100000832-B', '2021-10-01', '2110011100000832-F', '《分期支付协议》'], ['LN20211001142109802B642FA228C94', '2110011000000939-B', '2021-10-01', '2110011000000939-F', '《分期支付协议》'], ['LN20211001143051653192253DECDC7', '2110011200000768-B', '2021-10-01', '2110011200000768-F', '《分期支付协议》'], ['LN2021100114511445199B392C279DC', '2110011200000687-B', '2021-10-01', '2110011200000687-F', '《分期支付协议》'], ['LN20211001145116979D9D03AE9B4F6', '2110011200000672-B', '2021-10-01', '2110011200000672-F', '《分期支付协议》'], ['LN202110011519317308C18E1097B63', '2110011500000269-B', '2021-10-01', '2110011500000269-F', '《分期支付协议》']]\n"
     ]
    }
   ],
   "source": [
    "# 提取保理合同信息：\n",
    "from PyPDF2 import PdfFileReader\n",
    "from pathlib import Path\n",
    "baoli_path = Path('./baoli/')\n",
    "baoli_list = baoli_path.glob('*.pdf')\n",
    "\n",
    "baoli = [['贷款申请编号','保理合同编号','保理合同签署日期','基础合同/协议编号','基础合同/协议名称']] \n",
    "for i in baoli_list:\n",
    "    path = f'./baoli/{i.name}'\n",
    "    baoli_reader = PdfFileReader(path)\n",
    "    baoli_fields = baoli_reader.getFields()\n",
    "    temp = [i.stem.split('_')[0]]\n",
    "    for field in baoli_fields:\n",
    "        if field == 'contractNo':\n",
    "            contract_no = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(contract_no)\n",
    "            # print(contract_no)\n",
    "        elif field == 'signDate':\n",
    "            sign_date = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(sign_date)\n",
    "            # print(sign_date)\n",
    "        elif field == 'signContractNo':\n",
    "            sign_contract_no = list(baoli_fields[field].values())[-1]\n",
    "            temp.append(sign_contract_no)\n",
    "            # print(sign_contract_no)\n",
    "    temp.append('《分期支付协议》')\n",
    "    baoli.append(temp)\n",
    "print(baoli)\n",
    "\n",
    "    \n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "baoli"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[['基础合同编号', '商户住所', '商户统一社会信用代码', '商户法定代表人', '底层合同期限', '应收账款', '商户名称', '消费者姓名', '服务费用', '基础合同签署日期'], ['2109302200000057-F', '莞城街道东纵路莞城段2号1009铺', '91441900MA53GD4FXD', '夏明阳', 7, '1000.00', '东莞莞城天后医疗美容门诊有限公司', '赵杨', '0.00', '2021年30月9日'], ['2109302000000008-F', '高新区益州大道北段777号1幢2单元3楼301、302、303、304号', '91510100MA65YAHH2C', '李忠林', 7, '26800.00', '成都高新区熹美医疗美容门诊有限责任公司', '吴怡娴', '754.96', '2021年30月9日'], ['2110011100000243-F', '高新区浒墅关镇桑园路9号2号楼110室', '91320505MA1YUAMD7C', '陈先禹', 13, '30000.00', '苏州市高新区美君医疗美容有限公司', '葛恒娜', '1608.14', '2021年1月10日'], ['2110011100000871-F', '宝洲街336号店', '91350503MA339U5595', '王钦明', 7, '4000.00', '泉州市欧菲医疗美容有限公司', '杨景雪', '112.67', '2021年1月10日'], ['2110011100000832-F', '惠山万达商业广场64', '92320213MA1R2RRU59', '胡安敏', 13, '10000.00', '崇安区苗益生痘业美容院', '陈千爱', '533.21', '2021年1月10日'], ['2110011000000939-F', '卧龙路181号拓宝大厦商业三层', '92411300MA459X7M2Y', '杨玉兵', 7, '1850.00', '南阳市市辖区天后医疗美容门诊部', '李洁琼', '52.11', '2021年1月10日'], ['2110011200000768-F', '西宁市城西区西关大街36号6幢1-2层', '91630104MA756NPRX6', '何升娇', 13, '11000.00', '西宁摩尔口腔门诊有限公司', '力加', '0.00', '2021年1月10日'], ['2110011200000687-F', '福建省莆田市荔城区镇海街道顶务巷84号', '92350304MA2YENEW1A', '吴建超', 13, '5660.00', '莆田市荔城区女人花医疗美容门诊部', '王东花', '0.00', '2021年1月10日'], ['2110011200000672-F', '深圳市福田区沙头街道新华社区滨河路嘉洲豪园裙楼第1、2层02', '914403000725282500', '尤丽娜', 7, '18000.00', '深圳碧莲盛医疗美容门诊部', '唐海生', '0.00', '2021年1月10日'], ['2110011500000269-F', '江湾二路13号B座西边首层、二层', '91440604MA512WRB75', '汪晶晶', 7, '11850.00', '佛山禅城广肤门诊部有限公司', '杨剑威', '0.00', '2021年1月10日']]\n"
     ]
    }
   ],
   "source": [
    "# 提取分期协议信息\n",
    "fenqi_path = Path('./fenqi/')\n",
    "fenqi_list = fenqi_path.glob('*.pdf')\n",
    "fenqi = [['基础合同编号','商户住所',\n",
    "          '商户统一社会信用代码','商户法定代表人',\n",
    "          '底层合同期限','应收账款','商户名称','消费者姓名',\n",
    "          '服务费用',\n",
    "          '基础合同签署日期']]\n",
    "for j in fenqi_list:\n",
    "    path = f'./fenqi/{j.name}'\n",
    "    fenqi_reader = PdfFileReader(path)\n",
    "    fenqi_fields = fenqi_reader.getFields()\n",
    "    temp = []\n",
    "    date = []\n",
    "    for field in fenqi_fields:\n",
    "        if field == 'contractNo':\n",
    "            f_contract_no = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_contract_no)\n",
    "            # print(f_contract_no)\n",
    "        elif field == 'merchantCreditCode':\n",
    "            f_merchant_credit_code = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_merchant_credit_code)\n",
    "            # print(f_merchant_credit_code)\n",
    "        elif field == 'merchantAddress':\n",
    "            f_merchant_address = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(f_merchant_address)\n",
    "            # print(f_merchant_address)\n",
    "        elif field == 'legalPerson':\n",
    "            legalPerson = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(legalPerson)\n",
    "            # print(legalPerson)\n",
    "        elif field == 'merchantName':\n",
    "            merchantName = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(merchantName)\n",
    "            # print(merchantName)\n",
    "        elif field == 'userName':\n",
    "            userName = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(userName)\n",
    "            # print(userName)\n",
    "        elif field == 'money':\n",
    "            money = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(money)\n",
    "            # print(money)\n",
    "        elif field == 'periods':\n",
    "            periods = int(list(fenqi_fields[field].values())[-1])+1\n",
    "            temp.append(periods)\n",
    "            # print(periods)\n",
    "        elif field == 'serviceMoeny':\n",
    "            serviceMoeny = list(fenqi_fields[field].values())[-1]\n",
    "            temp.append(serviceMoeny)\n",
    "            # print(serviceMoeny)\n",
    "        elif field == 'yyyy':\n",
    "            year = list(fenqi_fields[field].values())[-1]\n",
    "            if year:\n",
    "                date.append(year)\n",
    "            else:\n",
    "                date.append('年为空')\n",
    "            # print(year)\n",
    "        elif field == 'mm':\n",
    "            month = list(fenqi_fields[field].values())[-1]\n",
    "            if month:   \n",
    "                date.append(month)\n",
    "            else:\n",
    "                date.append(\"月为空\")\n",
    "            # print(month)\n",
    "        elif field == 'dd':\n",
    "            day = list(fenqi_fields[field].values())[-1]\n",
    "            if day:\n",
    "                date.append(day)\n",
    "            else:\n",
    "                date.append(\"天为空\")\n",
    "    date = date[0]+'年'+date[1]+'月'+date[2]+'日'\n",
    "    temp.append(date)\n",
    "    fenqi.append(temp)\n",
    "print(fenqi)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[['基础合同编号',\n",
       "  '商户住所',\n",
       "  '商户统一社会信用代码',\n",
       "  '商户法定代表人',\n",
       "  '底层合同期限',\n",
       "  '应收账款',\n",
       "  '商户名称',\n",
       "  '消费者姓名',\n",
       "  '服务费用',\n",
       "  '基础合同签署日期'],\n",
       " ['2109302200000057-F',\n",
       "  '莞城街道东纵路莞城段2号1009铺',\n",
       "  '91441900MA53GD4FXD',\n",
       "  '夏明阳',\n",
       "  7,\n",
       "  '1000.00',\n",
       "  '东莞莞城天后医疗美容门诊有限公司',\n",
       "  '赵杨',\n",
       "  '0.00',\n",
       "  '2021年30月9日'],\n",
       " ['2109302000000008-F',\n",
       "  '高新区益州大道北段777号1幢2单元3楼301、302、303、304号',\n",
       "  '91510100MA65YAHH2C',\n",
       "  '李忠林',\n",
       "  7,\n",
       "  '26800.00',\n",
       "  '成都高新区熹美医疗美容门诊有限责任公司',\n",
       "  '吴怡娴',\n",
       "  '754.96',\n",
       "  '2021年30月9日'],\n",
       " ['2110011100000243-F',\n",
       "  '高新区浒墅关镇桑园路9号2号楼110室',\n",
       "  '91320505MA1YUAMD7C',\n",
       "  '陈先禹',\n",
       "  13,\n",
       "  '30000.00',\n",
       "  '苏州市高新区美君医疗美容有限公司',\n",
       "  '葛恒娜',\n",
       "  '1608.14',\n",
       "  '2021年1月10日'],\n",
       " ['2110011100000871-F',\n",
       "  '宝洲街336号店',\n",
       "  '91350503MA339U5595',\n",
       "  '王钦明',\n",
       "  7,\n",
       "  '4000.00',\n",
       "  '泉州市欧菲医疗美容有限公司',\n",
       "  '杨景雪',\n",
       "  '112.67',\n",
       "  '2021年1月10日'],\n",
       " ['2110011100000832-F',\n",
       "  '惠山万达商业广场64',\n",
       "  '92320213MA1R2RRU59',\n",
       "  '胡安敏',\n",
       "  13,\n",
       "  '10000.00',\n",
       "  '崇安区苗益生痘业美容院',\n",
       "  '陈千爱',\n",
       "  '533.21',\n",
       "  '2021年1月10日'],\n",
       " ['2110011000000939-F',\n",
       "  '卧龙路181号拓宝大厦商业三层',\n",
       "  '92411300MA459X7M2Y',\n",
       "  '杨玉兵',\n",
       "  7,\n",
       "  '1850.00',\n",
       "  '南阳市市辖区天后医疗美容门诊部',\n",
       "  '李洁琼',\n",
       "  '52.11',\n",
       "  '2021年1月10日'],\n",
       " ['2110011200000768-F',\n",
       "  '西宁市城西区西关大街36号6幢1-2层',\n",
       "  '91630104MA756NPRX6',\n",
       "  '何升娇',\n",
       "  13,\n",
       "  '11000.00',\n",
       "  '西宁摩尔口腔门诊有限公司',\n",
       "  '力加',\n",
       "  '0.00',\n",
       "  '2021年1月10日'],\n",
       " ['2110011200000687-F',\n",
       "  '福建省莆田市荔城区镇海街道顶务巷84号',\n",
       "  '92350304MA2YENEW1A',\n",
       "  '吴建超',\n",
       "  13,\n",
       "  '5660.00',\n",
       "  '莆田市荔城区女人花医疗美容门诊部',\n",
       "  '王东花',\n",
       "  '0.00',\n",
       "  '2021年1月10日'],\n",
       " ['2110011200000672-F',\n",
       "  '深圳市福田区沙头街道新华社区滨河路嘉洲豪园裙楼第1、2层02',\n",
       "  '914403000725282500',\n",
       "  '尤丽娜',\n",
       "  7,\n",
       "  '18000.00',\n",
       "  '深圳碧莲盛医疗美容门诊部',\n",
       "  '唐海生',\n",
       "  '0.00',\n",
       "  '2021年1月10日'],\n",
       " ['2110011500000269-F',\n",
       "  '江湾二路13号B座西边首层、二层',\n",
       "  '91440604MA512WRB75',\n",
       "  '汪晶晶',\n",
       "  7,\n",
       "  '11850.00',\n",
       "  '佛山禅城广肤门诊部有限公司',\n",
       "  '杨剑威',\n",
       "  '0.00',\n",
       "  '2021年1月10日']]"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "fenqi"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 198,
   "metadata": {},
   "outputs": [],
   "source": [
    "df1 = pd.DataFrame(baoli[1:],columns=baoli[0])\n",
    "df2 = pd.DataFrame(fenqi[1:],columns=fenqi[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 199,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>贷款申请编号</th>\n",
       "      <th>保理合同编号</th>\n",
       "      <th>保理合同签署日期</th>\n",
       "      <th>基础合同/协议编号</th>\n",
       "      <th>基础合同/协议名称</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LN20210930230141334B7B43DC9ED46</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LN20210930231229446436208C4FBA9</td>\n",
       "      <td>2109302000000008-B</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LN2021100111181824924236E86CD39</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LN202110011421043634D3F3FFBF5E5</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LN20211001142107022D4479A0DD011</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LN20211001142109802B642FA228C94</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LN20211001143051653192253DECDC7</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LN2021100114511445199B392C279DC</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LN20211001145116979D9D03AE9B4F6</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>LN202110011519317308C18E1097B63</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            贷款申请编号              保理合同编号    保理合同签署日期  \\\n",
       "0  LN20210930230141334B7B43DC9ED46  2110012100000004-B  2021-10-01   \n",
       "1  LN20210930231229446436208C4FBA9  2109302000000008-B  2021-09-30   \n",
       "2  LN2021100111181824924236E86CD39  2110011600000429-B  2021-10-01   \n",
       "3  LN202110011421043634D3F3FFBF5E5  2110011600000429-B  2021-10-01   \n",
       "4  LN20211001142107022D4479A0DD011  2110011600000429-B  2021-10-01   \n",
       "5  LN20211001142109802B642FA228C94  2110011600000429-B  2021-10-01   \n",
       "6  LN20211001143051653192253DECDC7  2110012100000004-B  2021-10-01   \n",
       "7  LN2021100114511445199B392C279DC  2110012100000004-B  2021-10-01   \n",
       "8  LN20211001145116979D9D03AE9B4F6  2110012100000004-B  2021-10-01   \n",
       "9  LN202110011519317308C18E1097B63  2110012100000004-B  2021-10-01   \n",
       "\n",
       "            基础合同/协议编号 基础合同/协议名称  \n",
       "0  2110012100000004-F  《分期支付协议》  \n",
       "1  2109302000000008-F  《分期支付协议》  \n",
       "2  2110011600000429-F  《分期支付协议》  \n",
       "3  2110011600000429-F  《分期支付协议》  \n",
       "4  2110011600000429-F  《分期支付协议》  \n",
       "5  2110011600000429-F  《分期支付协议》  \n",
       "6  2110012100000004-F  《分期支付协议》  \n",
       "7  2110012100000004-F  《分期支付协议》  \n",
       "8  2110012100000004-F  《分期支付协议》  \n",
       "9  2110012100000004-F  《分期支付协议》  "
      ]
     },
     "execution_count": 199,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 200,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>基础合同编号</th>\n",
       "      <th>商户名称</th>\n",
       "      <th>商户统一社会信用代码</th>\n",
       "      <th>商户法定代表人</th>\n",
       "      <th>商户住所</th>\n",
       "      <th>消费者姓名</th>\n",
       "      <th>应收账款</th>\n",
       "      <th>底层合同期限</th>\n",
       "      <th>服务费用</th>\n",
       "      <th>基础合同签署日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>成都高新区熹美医疗美容门诊有限责任公司</td>\n",
       "      <td>91510100MA65YAHH2C</td>\n",
       "      <td>李忠林</td>\n",
       "      <td>高新区益州大道北段777号1幢2单元3楼301、302、303、304号</td>\n",
       "      <td>吴怡娴</td>\n",
       "      <td>26800.00</td>\n",
       "      <td>7</td>\n",
       "      <td>754.96</td>\n",
       "      <td>2021年9月30日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>成都高新区熹美医疗美容门诊有限责任公司</td>\n",
       "      <td>91510100MA65YAHH2C</td>\n",
       "      <td>李忠林</td>\n",
       "      <td>高新区益州大道北段777号1幢2单元3楼301、302、303、304号</td>\n",
       "      <td>吴怡娴</td>\n",
       "      <td>26800.00</td>\n",
       "      <td>7</td>\n",
       "      <td>754.96</td>\n",
       "      <td>2021年9月30日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               基础合同编号                 商户名称          商户统一社会信用代码 商户法定代表人  \\\n",
       "0  2109302000000008-F  成都高新区熹美医疗美容门诊有限责任公司  91510100MA65YAHH2C     李忠林   \n",
       "1  2109302000000008-F  成都高新区熹美医疗美容门诊有限责任公司  91510100MA65YAHH2C     李忠林   \n",
       "2  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "3  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "4  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "5  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "6  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "7  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "8  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "9  2110011600000429-F           惠东县平山芬玲美容店  92441323MA4WUN1J1H     冯惠玲   \n",
       "\n",
       "                                   商户住所 消费者姓名      应收账款  底层合同期限    服务费用  \\\n",
       "0  高新区益州大道北段777号1幢2单元3楼301、302、303、304号   吴怡娴  26800.00       7  754.96   \n",
       "1  高新区益州大道北段777号1幢2单元3楼301、302、303、304号   吴怡娴  26800.00       7  754.96   \n",
       "2                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "3                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "4                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "5                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "6                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "7                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "8                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "9                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   6800.00      13  362.58   \n",
       "\n",
       "     基础合同签署日期  \n",
       "0  2021年9月30日  \n",
       "1  2021年9月30日  \n",
       "2  2021年10月1日  \n",
       "3  2021年10月1日  \n",
       "4  2021年10月1日  \n",
       "5  2021年10月1日  \n",
       "6  2021年10月1日  \n",
       "7  2021年10月1日  \n",
       "8  2021年10月1日  \n",
       "9  2021年10月1日  "
      ]
     },
     "execution_count": 200,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 201,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>贷款申请编号</th>\n",
       "      <th>保理合同编号</th>\n",
       "      <th>保理合同签署日期</th>\n",
       "      <th>基础合同/协议编号</th>\n",
       "      <th>基础合同/协议名称</th>\n",
       "      <th>基础合同编号</th>\n",
       "      <th>商户名称</th>\n",
       "      <th>商户统一社会信用代码</th>\n",
       "      <th>商户法定代表人</th>\n",
       "      <th>商户住所</th>\n",
       "      <th>消费者姓名</th>\n",
       "      <th>应收账款</th>\n",
       "      <th>底层合同期限</th>\n",
       "      <th>服务费用</th>\n",
       "      <th>基础合同签署日期</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>LN20210930230141334B7B43DC9ED46</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>成都高新区熹美医疗美容门诊有限责任公司</td>\n",
       "      <td>91510100MA65YAHH2C</td>\n",
       "      <td>李忠林</td>\n",
       "      <td>高新区益州大道北段777号1幢2单元3楼301、302、303、304号</td>\n",
       "      <td>吴怡娴</td>\n",
       "      <td>26800.00</td>\n",
       "      <td>7</td>\n",
       "      <td>754.96</td>\n",
       "      <td>2021年9月30日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>LN20210930231229446436208C4FBA9</td>\n",
       "      <td>2109302000000008-B</td>\n",
       "      <td>2021-09-30</td>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2109302000000008-F</td>\n",
       "      <td>成都高新区熹美医疗美容门诊有限责任公司</td>\n",
       "      <td>91510100MA65YAHH2C</td>\n",
       "      <td>李忠林</td>\n",
       "      <td>高新区益州大道北段777号1幢2单元3楼301、302、303、304号</td>\n",
       "      <td>吴怡娴</td>\n",
       "      <td>26800.00</td>\n",
       "      <td>7</td>\n",
       "      <td>754.96</td>\n",
       "      <td>2021年9月30日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>LN2021100111181824924236E86CD39</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>LN202110011421043634D3F3FFBF5E5</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>LN20211001142107022D4479A0DD011</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>LN20211001142109802B642FA228C94</td>\n",
       "      <td>2110011600000429-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>LN20211001143051653192253DECDC7</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>LN2021100114511445199B392C279DC</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>LN20211001145116979D9D03AE9B4F6</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>LN202110011519317308C18E1097B63</td>\n",
       "      <td>2110012100000004-B</td>\n",
       "      <td>2021-10-01</td>\n",
       "      <td>2110012100000004-F</td>\n",
       "      <td>《分期支付协议》</td>\n",
       "      <td>2110011600000429-F</td>\n",
       "      <td>惠东县平山芬玲美容店</td>\n",
       "      <td>92441323MA4WUN1J1H</td>\n",
       "      <td>冯惠玲</td>\n",
       "      <td>惠东县平山华侨城中航城一期2栋1层26号</td>\n",
       "      <td>朱碧容</td>\n",
       "      <td>6800.00</td>\n",
       "      <td>13</td>\n",
       "      <td>362.58</td>\n",
       "      <td>2021年10月1日</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            贷款申请编号              保理合同编号    保理合同签署日期  \\\n",
       "0  LN20210930230141334B7B43DC9ED46  2110012100000004-B  2021-10-01   \n",
       "1  LN20210930231229446436208C4FBA9  2109302000000008-B  2021-09-30   \n",
       "2  LN2021100111181824924236E86CD39  2110011600000429-B  2021-10-01   \n",
       "3  LN202110011421043634D3F3FFBF5E5  2110011600000429-B  2021-10-01   \n",
       "4  LN20211001142107022D4479A0DD011  2110011600000429-B  2021-10-01   \n",
       "5  LN20211001142109802B642FA228C94  2110011600000429-B  2021-10-01   \n",
       "6  LN20211001143051653192253DECDC7  2110012100000004-B  2021-10-01   \n",
       "7  LN2021100114511445199B392C279DC  2110012100000004-B  2021-10-01   \n",
       "8  LN20211001145116979D9D03AE9B4F6  2110012100000004-B  2021-10-01   \n",
       "9  LN202110011519317308C18E1097B63  2110012100000004-B  2021-10-01   \n",
       "\n",
       "            基础合同/协议编号 基础合同/协议名称              基础合同编号                 商户名称  \\\n",
       "0  2110012100000004-F  《分期支付协议》  2109302000000008-F  成都高新区熹美医疗美容门诊有限责任公司   \n",
       "1  2109302000000008-F  《分期支付协议》  2109302000000008-F  成都高新区熹美医疗美容门诊有限责任公司   \n",
       "2  2110011600000429-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "3  2110011600000429-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "4  2110011600000429-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "5  2110011600000429-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "6  2110012100000004-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "7  2110012100000004-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "8  2110012100000004-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "9  2110012100000004-F  《分期支付协议》  2110011600000429-F           惠东县平山芬玲美容店   \n",
       "\n",
       "           商户统一社会信用代码 商户法定代表人                                  商户住所 消费者姓名  \\\n",
       "0  91510100MA65YAHH2C     李忠林  高新区益州大道北段777号1幢2单元3楼301、302、303、304号   吴怡娴   \n",
       "1  91510100MA65YAHH2C     李忠林  高新区益州大道北段777号1幢2单元3楼301、302、303、304号   吴怡娴   \n",
       "2  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "3  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "4  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "5  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "6  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "7  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "8  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "9  92441323MA4WUN1J1H     冯惠玲                  惠东县平山华侨城中航城一期2栋1层26号   朱碧容   \n",
       "\n",
       "       应收账款  底层合同期限    服务费用    基础合同签署日期  \n",
       "0  26800.00       7  754.96  2021年9月30日  \n",
       "1  26800.00       7  754.96  2021年9月30日  \n",
       "2   6800.00      13  362.58  2021年10月1日  \n",
       "3   6800.00      13  362.58  2021年10月1日  \n",
       "4   6800.00      13  362.58  2021年10月1日  \n",
       "5   6800.00      13  362.58  2021年10月1日  \n",
       "6   6800.00      13  362.58  2021年10月1日  \n",
       "7   6800.00      13  362.58  2021年10月1日  \n",
       "8   6800.00      13  362.58  2021年10月1日  \n",
       "9   6800.00      13  362.58  2021年10月1日  "
      ]
     },
     "execution_count": 201,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df3 = pd.concat([df1,df2],join='outer',axis=1)\n",
    "df3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "metadata": {},
   "outputs": [],
   "source": [
    "df3.to_excel(r'final.xlsx',sheet_name='总表')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "metadata": {},
   "outputs": [],
   "source": [
    "#调整格式\n",
    "import xlwings as xw\n",
    "app = xw.App(visible=False,add_book=False)\n",
    "workbook = app.books.open(r'final.xlsx')\n",
    "worksheet = workbook.sheets['总表']\n",
    "worksheet.autofit()\n",
    "workbook.save()\n",
    "workbook.close()\n",
    "app.quit()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "interpreter": {
   "hash": "6f8ee94c255eb1f45edb80e83721093c1db1e2ea85447c0854292673b957abb8"
  },
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
